﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using uMES.LeanManufacturing.ParameterDTO;
using uMES.LeanManufacturing.DBUtility;
using System.Data;
using System.Collections;

namespace uMES.LeanManufacturing.ReportBusiness
{
    public class uMESRoleEmployeeBusiness
    {
        public DataTable GetEmployeeInfo(Dictionary<string,string> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"SELECT e.fullname, f.factoryname, f.description, f.factoryid, e.employeeid, e.teamid, e.employeename,
                               e.password,e.InspectorNo,t.teamname 
                        FROM sessionvalues sv 
                        LEFT JOIN employee e ON sv.employeeid = e.employeeid 
                        LEFT JOIN factory f ON sv.factoryid = f.factoryid 
                        LEFT JOIN team t ON t.teamid = e.teamid 
                        WHERE 1 = 1 " );
            if (para.ContainsKey("EmployeeName"))
            {
                sb.Append("AND LOWER(e.employeename) LIKE '%" + para["EmployeeName"].ToLower() + "%' OR LOWER(e.cardnumber) LIKE '%" + para["EmployeeName"].ToLower() +"%'");
            }

            DataTable dt = OracleHelper.GetDataTable(sb.ToString());

            return dt;
        }

        /// <summary>
        /// 获取角色人员信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public uMESPagingDataDTO GetRoleEmployeeData(Dictionary<string, string> para)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("SELECT re.id,rd.rolename,re.roleid,re.employeeid,re.createdate,e.fullname,e1.fullname AS createfullname,");
            strSql.AppendLine("       t.teamname,f.factoryname, f.description");
            strSql.AppendLine("FROM roleemployee re");
            strSql.AppendLine("LEFT JOIN employee  e ON e.employeeid = re.employeeid");
            strSql.AppendLine("LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid");
            strSql.AppendLine("LEFT JOIN factory f ON sv.factoryid = f.factoryid");
            strSql.AppendLine("LEFT JOIN team t ON t.teamid = e.teamid");
            strSql.AppendLine("LEFT JOIN employee  e1 ON e1.employeeid = re.createempid");
            strSql.AppendLine("LEFT JOIN factory f ON sv.factoryid = f.factoryid");
            strSql.AppendLine("left join roledef rd on rd.roleid=re.roleid");
           // strSql.AppendLine("LEFT JOIN team t ON t.teamid = e.teamid");
            strSql.AppendLine("WHERE 1=1");
            string condition = "";
            if (para.ContainsKey("FullName"))
            {
                condition += string.Format(" and e.fullname like '%{0}%'", para["FullName"]);
            }

            if (para.ContainsKey("RoleName"))
            {
                condition += string.Format(" and re.rolename like '%{0}%'", para["RoleName"]);
            }

            strSql.AppendLine(condition);

            strSql.AppendLine("ORDER BY re.rolename,e.fullname");

            uMESPagingDataDTO result = new uMESPagingDataDTO();

            result = OracleHelper.GetPagingDataIns(strSql.ToString(), int.Parse(para["CurrentPageIndex"]), int.Parse(para["PageSize"]));
            result.DBTable.Columns.Add("isDeleted");
            if (result.DBTable.Rows.Count>0)
            {
                DataTable dtRole = GetRole();
                if (dtRole.Rows.Count>0)
                {
                    DataRow[] dr;
                    for (int i=0;i<result.DBTable.Rows.Count;i++)
                    {
                        string strRoleId = string.Empty;
                        result.DBTable.Rows[i]["isDeleted"] = "0";//角色未删除
                        if (!string.IsNullOrEmpty(result.DBTable.Rows[i]["roleid"].ToString()))
                        {
                            strRoleId = result.DBTable.Rows[i]["roleid"].ToString();
                            dr = dtRole.Select("roleid='"+ strRoleId + "'");
                            if (dr.Length==0)
                            {
                                result.DBTable.Rows[i]["isDeleted"] = "1";//角色已删除
                            }
                        }
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 查询角色
        /// </summary>
        /// <returns></returns>
        public DataTable GetRole()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("SELECT r.roleid,r.rolename FROM roledef r WHERE r.isdisplay = 1");
            return OracleHelper.Query(strSql.ToString()).Tables[0];
        }

        /// <summary>
        /// 查询角色
        /// </summary>
        /// <returns></returns>
        public DataTable GetRoleEmployee(String strid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("SELECT re.id,re.rolename,re.roleid,re.employeeid,e.fullname,t.teamname,f.factoryname, f.description,e.employeename ");
            strSql.AppendLine("FROM roleemployee re");
            strSql.AppendLine("LEFT JOIN employee  e ON e.employeeid = re.employeeid");
            strSql.AppendLine("LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid");
            strSql.AppendLine("LEFT JOIN factory f ON sv.factoryid = f.factoryid");
            strSql.AppendLine("LEFT JOIN team t ON t.teamid = e.teamid");
            strSql.AppendLine("WHERE re.roleid='" + strid + "'");
            return OracleHelper.Query(strSql.ToString()).Tables[0];
        }

        /// <summary>
        /// 删除角色人员
        /// </summary>
        /// <param name="strID"></param>
        /// <returns></returns>
        public Boolean DelRoleEmployee(string strID)
        {
            ArrayList SQLStringList = new ArrayList();

            //删除 
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine(string.Format("DELETE roleemployee WHERE id = '{0}'", strID));

            SQLStringList.Add(strSQL.ToString());
            OracleHelper.ExecuteSqlTran(SQLStringList);
            return true;
        }

        /// <summary>
        /// 删除角色关联的所有人员信息
        /// </summary>
        /// <param name="strID"></param>
        /// <returns></returns>
        public Boolean DelRoleEmployeeByRoleID(string strRoleID)
        {
            ArrayList SQLStringList = new ArrayList();

            //删除 
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine(string.Format("DELETE roleemployee WHERE roleid = '{0}'", strRoleID));

            SQLStringList.Add(strSQL.ToString());
            OracleHelper.ExecuteSqlTran(SQLStringList);
            return true;
        }

        /// <summary>
        /// 保存人员角色
        /// </summary>
        /// <param name="para"></param>
        /// <param name="dtMenuChild"></param>
        /// <returns></returns>
        public ResultModel SaveRoleEmployeeInfo(Dictionary<string, string> para, DataTable dt)
        {
            ResultModel result = new ResultModel(false, "");
            ArrayList SQLStringList = new ArrayList(); 
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    StringBuilder strSQL = new StringBuilder();
                    strSQL.AppendLine("INSERT INTO roleemployee(id,rolename,roleid,employeeid,createempid,createdate)");
                    strSQL.AppendLine("VALUES(");
                    strSQL.AppendLine(string.Format("'{0}',", Guid.NewGuid().ToString()));
                    strSQL.AppendLine(string.Format("'{0}',", para["rolename"]));
                    strSQL.AppendLine(string.Format("'{0}',", para["roleid"]));
                    strSQL.AppendLine(string.Format("'{0}',", row["employeeid"]));
                    strSQL.AppendLine(string.Format("'{0}',", para["CreateEmployeeID"]));
                    strSQL.AppendLine(string.Format("TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS')", para["CreateDate"]));
                    strSQL.AppendLine(")");
                    SQLStringList.Add(strSQL.ToString());
                }
            }
            OracleHelper.ExecuteSqlTran(SQLStringList);
            result.IsSuccess = true;
            return result;
        }


        /// <summary>
        /// 更新人员角色
        /// </summary>
        /// <param name="para"></param>
        /// <param name="dtMenuChild"></param>
        /// <returns></returns>
        public ResultModel UpdateRoleEmployee(Dictionary<string, string> para, DataTable dt)
        {
            ResultModel result = new ResultModel(false, "");
            ArrayList SQLStringList = new ArrayList(); 
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine(string.Format("DELETE roleemployee WHERE roleid = '{0}'", para["roleid"].ToString())); 
            SQLStringList.Add(strSQL.ToString());
  
            foreach (DataRow row in dt.Rows)
            {
                strSQL = new StringBuilder();
                strSQL.AppendLine("INSERT INTO roleemployee(id,rolename,roleid,employeeid,createempid,createdate)");
                strSQL.AppendLine("VALUES(");
                strSQL.AppendLine(string.Format("'{0}',", Guid.NewGuid().ToString()));
                strSQL.AppendLine(string.Format("'{0}',", para["rolename"]));
                strSQL.AppendLine(string.Format("'{0}',", para["roleid"]));
                strSQL.AppendLine(string.Format("'{0}',", row["employeeid"]));
                strSQL.AppendLine(string.Format("'{0}',", para["CreateEmployeeID"]));
                strSQL.AppendLine(string.Format("TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS')", para["CreateDate"]));
                strSQL.AppendLine(")");
                SQLStringList.Add(strSQL.ToString());
            }
            OracleHelper.ExecuteSqlTran(SQLStringList);
            result.IsSuccess = true;
            return result;
        }
    }

}
